import express from 'express';
import dayjs from 'dayjs';
import mysql from 'mysql2';
import { customAlphabet  } from 'nanoid';

const nanoid = customAlphabet('0123456789ABCDEF', 10);

const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'root',
  database: 'express'
});

const router = express.Router();

const url = '/student';

router.get(url, (req,resp) => {
    console.log('GET');
    let select = 'SELECT id, name, gender, birthdate, hometown FROM t_students ORDER BY 1';
    connection.query(select, (err, results, fields) => {
        if( err ) {
            resp.json({ 
                success: false, 
                message: '查询失败', 
                reason: err.message 
            });
            return;
        }
        results.forEach( row => {
            row.birthdate = dayjs(row.birthdate).format('YYYY-MM-DD')
        })
        resp.json({students: results});
    });
});

router.post(url, (req,resp) => {
    console.log('POST');
    // 解构赋值
    const { name, gender, birthdate, hometown } = req.body ;
    let insert = 'INSERT INTO t_students (name, gender, birthdate, hometown, id) VALUES (?, ?, ?, ?, ?)';
    // 将数据按照参数占位符的次序依次添加到数组中
    let params = [name, gender, birthdate, hometown, nanoid() ];
    connection.query(insert, params, (err, results, fields) => {
        if( err ) {
            resp.json({ 
                success: false, 
                message: '保存失败', 
                reason: err.message 
            });
            return;
        }
        if( results.affectedRows != 1 ) {
            resp.json({ success: false, message: '保存失败' });
            return;
        }
        resp.json({ success: true, message: '保存成功'});
    });
});

router.put(url, (req,resp) => {
    console.log('PUT');
    const { id, name, gender, hometown } = req.body;
    let birthdate = req.body.dateOfBirth;
    birthdate = birthdate ? birthdate : req.body.birthdate;
    const params = [ name, gender, birthdate, hometown, id ];
    let update = 'UPDATE t_students SET name=?, gender=?, birthdate=?, hometown=? WHERE id = ?';
    connection.query(update, params, (err, results, fields) => {
        if(err) {
            resp.json({ 
                success: false, 
                message: '更新失败', 
                reason: err.message 
            });
            return;
        }
        if( results.affectedRows != 1 ) {
            resp.json({ success: false, message: '更新失败' });
            return;
        }
        resp.json({ success: true, message: '更新成功'});
    });
});

router.delete(url, (req,resp) => {
    console.log('DELETE', req.query );
    const { id } = req.query;
    const remove = 'DELETE FROM t_students WHERE id = ?';
    connection.query(remove, id, (err, results, fields) => {
        if(err) {
            resp.json({ 
                success: false, 
                message: '删除失败', 
                reason: err.message 
            });
            return;
        }
        if( results.affectedRows != 1 ) {
            resp.json({ success: false, message: '删除失败' });
            return;
        }
        resp.json({ success: true, message: '删除成功' });
    });
});

export default router;